/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package csse3004.GD.DA;

import csse3004.GD.PM.PollMasterService;
import csse3004.GD.Poll;
import csse3004.GD.PM.ParticipantDetail;
import csse3004.GD.Participant;
import csse3004.GD.ParticipantListTemplate;
import csse3004.GD.WBUser.PollUsers;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.logging.Logger;

/**
 *
 * @author Tom McNair - S41758984
 */
public class PollMasterDA extends GenericDA implements PollMasterService {

    private static final Logger m_logger = Logger.getLogger(LoginDA.class.getName());

    public ArrayList getPolls(int userID) {
        String sql = "SELECT Poll.Poll_ID, Poll.Poll_Name, Poll.Public_Result, "
                + "Poll.Anonymous, "
                + "Poll.Admin_ID FROM Poll, Poll_Master, Users "
                + "WHERE USERS.User_ID = Poll_Master.Master_ID AND "
                + "Poll.Poll_ID = Poll_Master.Poll_ID AND "
                + "Users.User_ID = " + userID;

        ArrayList<Poll> pollArray = new ArrayList<Poll>();
        Poll poll;

        m_logger.info(sql);

        try {
            ResultSet results = DatabaseQuery(sql);

            while (results.next()) {
                poll = new Poll();

                poll.setId(results.getInt("Poll_ID"));
                poll.setName(results.getString("Poll_Name"));

                if (results.getString("Public_Result").equals("Yes")) {
                    poll.setPublicResult();
                } else {
                    poll.setPrivateResult();
                }

                if (results.getString("Anonymous").equals("Yes")) {
                    poll.setAnonymous(true);
                } else {
                    poll.setAnonymous(false);
                }

                pollArray.add(poll);
            }
            return pollArray;

        } catch (Exception ex) {
            m_logger.info("getPolls() failed\n");
            m_logger.info(ex.toString());
        }
        return null;
    }

    /**
     * Sets if the poll is open to the world or not
     * @param open if the poll is meant to be open
     * @param Pid the question ID to open 
     * @return if the function was successful
     */
    public boolean setQuestionOpen(int questionID, boolean open) {
        String sql = "SELECT Status FROM Question WHERE Question_ID = " + questionID+" AND Deleted = 'No' AND Overwritten = 'No'";

        try {
            ResultSet results = DatabaseQuery(sql);

            results.next();
            int questionStatus = results.getInt("Status");
            
            if (open) {
                if (questionStatus == 1) {
                    m_logger.info("Question already open");
                    return false;
                } else {
                    if (DatabaseInsert("UPDATE Question SET Status = 1 WHERE Question_ID = "+questionID+" AND Deleted = 'No' AND Overwritten = 'No'")) {
                        return true;
                    } else {
                        return false;
                    }
                }
            } else {
                if (questionStatus == 1) {
                    m_logger.info("Question hasn't been opened yet!");
                    return false;
                } else if (questionStatus == 2) {
                    m_logger.info("Question is already closed!");
                    return false;
                } else {
                    if (DatabaseInsert("UPDATE Question SET Status = 2 WHERE Question_ID = "+questionID+" AND Deleted = 'No' AND Overwritten = 'No'")) {
                        return true;
                    } else {
                        return false;
                    }
                }
            }
        } catch (Exception ex) {
            m_logger.info("getMCAnswers() failed\n");
            m_logger.info(ex.toString());
        }
        return false;
    }

    /**
     * Set the location of the specified poll
     * @param Pid ID of the poll
     * @param longitude longitude to record
     * @param latitude latitude to record
     * @return if the function was successful
     */
    public boolean setLocation(int Pid, String name, String longitude, String latitude) {
        int locationId = getNextID("Location");
        try {
                String sql = "INSERT INTO Location VALUES (" + locationId + ", '" + name + "', " + latitude + ", " + longitude + ")";
                String sql2 = "INSERT INTO Poll_Location VALUES (" + Pid + ", " + locationId + ")";
                if ((DatabaseInsert(sql) == false) || (DatabaseInsert(sql2) == false)) {
                    return false;
                } else {
                    return true;
                }
        } catch (Exception ex) {
            m_logger.info("Set Location failed\n");
            m_logger.info(ex.toString());
            return false;
        }
    }

    /**
     * Get the location of the specified poll
     * @param Pid ID of the poll
     * @return float array containing the latitude and longitude of the poll,
     * [0] = latitude, [1] = longitude
     */
    public double[] getLocation(int Pid) {
        String sql = "Select Location.Latitude, Location.Longitude from Location,"
                + " Poll_Location WHERE Location.Location_ID = Poll_Location.Location_ID"
                + " AND Poll_Location.Poll_ID =" + Pid;
        m_logger.info(sql);
        double[] location = new double[2];
        try {
            ResultSet results = DatabaseQuery(sql);
            if (results.next())  {
                m_logger.info("Stage 1");
                location[0] = results.getFloat("Latitude");
                m_logger.info("Stage 2");
                location[1] = results.getFloat("Longitude");
                return location;
            } else {
                m_logger.info("getLocation failed due to a lack of results\n");
                return null;
            }
        } catch (Exception ex) {
            m_logger.info("getLocation failed\n");
            m_logger.info(ex.toString());
            return null;
        }
    }

    /** A function that retrieves all the different templates.
     * 
     * @return An arrayList of Templates (ID and Name)
     */
    public ArrayList<ParticipantListTemplate> getTemplates() {
        ArrayList<ParticipantListTemplate> templateList = new ArrayList<ParticipantListTemplate>();
        ParticipantListTemplate template;

        String sql = "SELECT * FROM Participant_List_Template";

        try {
            ResultSet results = DatabaseQuery(sql);

            while (results.next()) {
                template = new ParticipantListTemplate();
                template.setName(results.getString("Name"));
                m_logger.info(template.getName());
                template.setTemplateID(results.getInt("Template_ID"));
                m_logger.info("ID"+template.getTemplateID());
                templateList.add(template);
            }
        } catch (Exception ex) {
            m_logger.info("getTemplates() failed\n");
            m_logger.info(ex.toString());
        }

        return templateList;
    }

    /**
     * A function that retrieves all the demographics for a specified Participant List
     * 
     * @param listID ID of the Participant List
     * @return Returns an ArrayList of demographic strings
     */
    public ArrayList<String> getDemographics(int listID) {
        String sql = "SELECT Demographic.Name FROM Demographic, List_Demographic "
                + "WHERE Demographic.Demographic_ID = List_Demographic.Demographic_ID "
                + "AND List_Demographic.List_ID = " + listID;
        ArrayList<String> demographicList = new ArrayList<String>();
        try {
            ResultSet results = DatabaseQuery(sql);
            while (results.next()) {
                demographicList.add(results.getString("Name"));
            }
        } catch (Exception ex) {
            m_logger.info("getDemographics() failed\n");
            m_logger.info(ex.toString());
        }
        return demographicList;
    }

    /**
     * A function that retrieves all the demographics for a specified Participant List Template
     * 
     * @param listID ID of the Participant List Template
     * @return Returns an ArrayList of demographic strings
     */
    public ArrayList<String> getTemplateDemographics(int templateID) {
        String sql = "SELECT * FROM Demographic_Template WHERE Template_ID = " + templateID;
        ArrayList<String> demographicList = new ArrayList<String>();
        try {
            ResultSet results = DatabaseQuery(sql);
            while (results.next()) {
                demographicList.add(results.getString("Name"));
            }
        } catch (Exception ex) {
            m_logger.info("getTemplateDemographics() failed\n");
            m_logger.info(ex.toString());
        }
        return demographicList;
    }

    /**
     * A function that adds a list of Demographics to the database under a specific Participant List
     * 
     * @param listID ID of the Participant List Template
     * @return Returns an ArrayList of demographic strings
     */
    public boolean addDemographics(int listID, ArrayList<String> demographicList) {
        int demographicID = getNextID("Demographic");
        for (int i = 0; i < demographicList.size(); i++) {
            try {
                if (!checkFieldExistsString("Demographic", "Name", demographicList.get(i))) {
                    DatabaseInsert("INSERT INTO Demographic VALUES(" + demographicID + ", " + demographicList.get(i) + ")");
                }
                DatabaseInsert("INSERT INTO List_Demographic VALUES(" + listID + ", " + demographicID + ")");
                demographicID++;
            } catch (Exception ex) {
                m_logger.info("addDemographics() failed\n");
                m_logger.info(ex.toString());
                return false;
            }
        }
        return true;
    }

    /** A function that returns an ArrayList of the ID's of the participants in a specific list
     * @param listID The id of the participant list
     * @return returns an ArrayList participant ID's
     */
    public ArrayList<Participant> getParticipants(int listID) {
        ArrayList<Participant> participantList = new ArrayList<Participant>();
        Participant participant = new Participant();
        String sql = "SELECT * FROM Participant WHERE List_ID = " + listID;

        try {
            ResultSet results = DatabaseQuery(sql);
            while (results.next()) {
                participant.setParticipantID(results.getInt("User_ID"));
                participant.setDemographic(results.getInt("Demographic_ID"));
                participantList.add(participant);
            }
            return participantList;
        } catch (Exception ex) {
            m_logger.info("getParticipants() failed\n");
            m_logger.info(ex.toString());
            return null;
        }
    }

    /** A function that returns an ArrayList of the ID's of the participants in a specific list
     * @param listID The id of the participant list
     * @return returns an ArrayList participant ID's
     */
    public ArrayList<Participant> getParticipantsPoll(int pollID) {
        ArrayList<Participant> participantList = new ArrayList<Participant>();
        Participant participant = new Participant();
        String sql = "SELECT Participant.User_ID, Participant.Demographic_ID FROM Participant, Participant_List WHERE Participant.List_ID = Participant_List.List_ID"
                + " AND Participant_List.Poll_ID = " + pollID;

        try {
            ResultSet results = DatabaseQuery(sql);
            while (results.next()) {
                participant.setParticipantID(results.getInt("User_ID"));
                participant.setDemographic(results.getInt("Demographic_ID"));
                participantList.add(participant);
            }
            return participantList;
        } catch (Exception ex) {
            m_logger.info("getParticipantsPoll() failed\n");
            m_logger.info(ex.toString());
            return null;
        }
    }

    /**
     * A Function that retrieves all the details for a participant
     * @param participantID
     * @return an ArrayList of Participant Details
     */
    public ArrayList<ParticipantDetail> getParticipantDetails(int participantID) {
        String sql = "SELECT * FROM Participant_Detail WHERE User_ID = " + participantID;
        ParticipantDetail detail = new ParticipantDetail();
        ArrayList<ParticipantDetail> detailList = new ArrayList<ParticipantDetail>();

        try {
            ResultSet results = DatabaseQuery(sql);
            while (results.next()) {
                detail.setKey(results.getString("Key"));
                detail.setValue(results.getString("Value"));
                detail.setParticipantID(participantID);

                detailList.add(detail);
            }
            return detailList;
        } catch (Exception ex) {
            m_logger.info("getParticipantDetails() failed\n");
            m_logger.info(ex.toString());
            return null;
        }
    }

    public boolean sendMessage(boolean privateMessage, int senderID, int receiverID, String header, String body) {
        int messageID = getNextID("Message");
        String sql = "INSERT INTO Message VALUES(" + messageID + ", '" + header + "', '" + body + "', 'No', 'No', " + senderID + ", " + receiverID + ")";

        try {
            if (!DatabaseInsert(sql)) {
                return false;
            }
        } catch (Exception ex) {
            m_logger.info("sendMessage() failed\n");
            m_logger.info(ex.toString());
            return false;
        }
        return true;
    }

    public final boolean setNewObject(int questionID, String HTML) {
        int ID = getNextID("Object");
        String sql = "INSERT INTO Object VALUES(" + ID + ", '" + HTML + "', " + questionID + ")";
        try {
             return DatabaseInsert(sql);
        } catch (Exception ex) {
            m_logger.info("setNewObject() failed\n");
            m_logger.info(ex.toString());
            return false;
        }
    }

    public final ArrayList<String> getObjectHTML(int questionID) {
        String sql = "SELECT Html FROM Object WHERE Question_ID = " + questionID;
        ArrayList<String> objectList = new ArrayList<String>();

        try {
            ResultSet results = DatabaseQuery(sql);
            while (results.next()) {
                objectList.add(results.getString("HTML"));
            }
            return objectList;
        } catch (Exception ex) {
            m_logger.info("getObjectHTML() failed\n");
            m_logger.info(ex.toString());
            return null;
        }
    }

    public final boolean openQuestion(int questionID) {
        String sql = "UPDATE Question SET Status = 1 WHERE Question_ID = " + questionID;

        try {
             return DatabaseInsert(sql);
        } catch (Exception ex) {
            m_logger.info("openQuestion() failed\n");
            m_logger.info(ex.toString());
            return false;
        }
    }

    public final boolean closeQuestion(int questionID) {
        String sql = "UPDATE Question SET Status = 2 WHERE Question_ID = " + questionID;
        m_logger.info(sql);
        try {
            return DatabaseInsert(sql);
        } catch (Exception ex) {
            m_logger.info("closeQuestion() failed\n");
            m_logger.info(ex.toString());
            return false;
        }
    }

    public final boolean toggleQuestionStatus(int questionID) {
        String sql = "SELECT * FROM Question WHERE Question_ID = " + questionID;

        try {
            ResultSet results = DatabaseQuery(sql);
            while (results.next()) {
                if (results.getInt("Status") == 0 || results.getInt("Status") == 2) {
                    if (openQuestion(questionID)) {
                        return true;
                    }
                } else {
                    if (closeQuestion(questionID)) {
                        return true;
                    }
                }
            }
            return false;
        } catch (Exception ex) {
            m_logger.info("toggleQuestionStatus() failed\n");
            m_logger.info(ex.toString());
            return false;
        }
    }

    /**
     * Sets the specified questions results to be public/private
     * @param qid id of the question to set
     * @param isPublic if the question is to be public or not
     * @return true on success
     */
    public boolean setQuestionPublic(int qid, boolean isPublic) {
        String publicString = (isPublic) ? "Yes" : "No";

        String sql = "UPDATE Question SET Public = '" + publicString + "' WHERE Question_ID = " + qid;
        m_logger.info(sql);
        try {
            return DatabaseInsert(sql);
        } catch (Exception Ex) {
            m_logger.info("setQuestionPublic(" + qid + "," + isPublic + ") failed");
            m_logger.info(Ex.toString());
            return false;
        }
    }

    /**
     * Open a question with the given time (seconds), if the time is 0, keep open permanently
     *@param qid the question ID
     *@param openTime length of time (in seconds) to keep the question open for, 0 if it is to stay open
     **/
    public boolean openQuestion(int qid, int openTime) {
        long time;
        java.util.Date today = new java.util.Date();
        if (openTime == 0) {
            time = new java.util.GregorianCalendar(2063, 4, 5).getTimeInMillis();
        } else {
            time = new java.util.Date(today.getTime()).getTime() + (openTime * 1000);
        }

        String sql = "UPDATE Question set Status = 1, Close_Time = '" + time + "' WHERE Question_ID =" + qid;
        m_logger.info(sql);
        try {
            return DatabaseInsert(sql);
        } catch (Exception Ex) {
            m_logger.info("openQuestion(" + qid + ", " + openTime + ") failed\n");
            m_logger.info(Ex.toString());
            return false;
        }

    }

    /**
     * Add a response to the database, creating the user if required
     * If the user has submitted more then the allowed responses, then this will 
     * overwrite the previous answer(s) (in the case where the question was 
     * restarted)
     * @param responseCardID the card with which the response was made
     * @param response the actual response
     * @param questionID question that this is in response to.
     * @return 1 if successful AND there were no results over-written, 
     *         2 if there were results overwritten but still successful, 
     *        -1 if the poll is closed and so responses can't be written, 
     *        -2 for general error 
     */
    public int addResponse(String responseCardID, String response, int questionID, boolean isMC) {
        m_logger.info("adding response");

        String sql = "SELECT * FROM Users WHERE User_ID = " + responseCardID;

        try {
            if (getQuestionStatus(questionID) == 1) {
                ResultSet results = DatabaseQuery(sql);
                if (!results.next()) {
                    DatabaseInsert("INSERT INTO Users(User_ID, Type) VALUES(" + responseCardID + ", 'Keypad')");
                }
                if (isMC) {
                    int responses = Integer.parseInt(response);
                    int responseArray[] = new int[10];
                    int i = 0;
                    while (responses > 0) { 
                        responseArray[i] = responses % 10;
                        responses = responses / 10; 
                        i++;
                    }
                    int j = 1;
                    while (i > 0) {
                        sql = "INSERT INTO MC_RESPONSE VALUES("+getNextID("SR_RESPONSE")+","+responseArray[i]+",'"+j+"',"+questionID+","+responseCardID+")";
                        DatabaseInsert(sql);
                        j++;
                        return 1;
                    }

                } else if (!isMC) {
                    sql = "INSERT INTO SR_RESPONSE VALUES("+getNextID("SR_RESPONSE")+",'"+response+"',"+questionID+","+responseCardID+")";
                    DatabaseInsert(sql);
                    return 1;
                } else {
                    return -2;
                }
            } else {
                return -1;
            }
            return 2;
        } catch (Exception Ex) {
            m_logger.info("addResponse(responseCardID,response,questionID) failed\n");
            m_logger.info(Ex.toString());
            return -2;
        }
    }
    
    public boolean removeResponse(String responseCardID, int questionID, boolean isMC) {
        String sql;
        if (isMC) {
            sql = "DELETE FROM MC_Response WHERE User_ID = "+responseCardID+" AND Question_ID = "+questionID;
        } else {
            sql = "DELETE FROM SR_Response WHERE User_ID = "+responseCardID+" AND Question_ID = "+questionID;
        }
        try {
            return DatabaseInsert(sql);
        } catch (Exception Ex) {
            m_logger.info("removeResponse() failed\n");
            m_logger.info(Ex.toString());
            return false;
        }
    }
    
    public int getMCAnswerCount(int questionID) {
        String sql = "SELECT Count(*) as 'Potatoes' FROM MC_Question where Question_ID = "+questionID;
        try {
            ResultSet results = DatabaseQuery(sql);
            if (results.next()) {
                return results.getInt("Potatoes");
            } else {
                return -1;
            }
        } catch (Exception Ex) {
            m_logger.info("removeResponse() failed\n");
            m_logger.info(Ex.toString());
            return -1;
        }
    }

    public int getGraphType(int qid) {
        String sql = "SELECT Style FROM MC_Question WHERE MC_Question_ID = " + qid;
        try {
           ResultSet results = DatabaseQuery(sql);
           if (results.next()) {
               return results.getInt("Style");
           } else {
               return 0;
           }
        } catch (Exception Ex) {
            m_logger.info("getGraphType("+qid+") failed\n");
            m_logger.info(Ex.toString());
            return 0;
        }
    }
    
    public boolean addParticipant(int listID, int userID, int demographicID) {
        String sql = "INSERT INTO Demographic_Template VALUES("+listID+","+userID+","+demographicID+")";
        
        try {
            if(DatabaseInsert(sql)) {
                return true;
            } else {
                return false;
            }
        } catch (Exception Ex) {
            m_logger.info("addParticipant() failed\n");
            m_logger.info(Ex.toString());
            return false;
        }
    }
    
        /**
     * Will set the next/previous question in the database.
     *
     *@param isNext If the next question (true) or the previous question (false) should be set
     *@param  pollid the id of the poll to change
     *@return the new QuestionID or -1 on error or 0 if there are no more polls to set
     * TODO TOM I don't understand this code, could you make it return 0 if there are no more questions to be set??
     **/
    public int setNext(boolean isNext, int pid, int seconds) {
        int activeQuestion = getActiveQuestionID(pid);
        m_logger.info("Active Question: " + activeQuestion);

        String sql = "SELECT * FROM Question WHERE Poll_ID = " + pid;
        m_logger.info(sql);
        try {
            ResultSet results = DatabaseQuery(sql);
            while (results.next()) {
                if (results.getInt("Question_ID") == activeQuestion) {
                    if (isNext) {
                        if (results.next()) {
                            int newQuestionID = results.getInt("Question_ID");
                            DatabaseInsert("UPDATE Poll SET Active_Question = " + newQuestionID + " WHERE Poll_ID =" + pid);
                            //closeQuestion(activeQuestion);
                            openQuestion(newQuestionID, seconds);
                            return newQuestionID;
                        }
                        return -1;  
                    } else {
                        if (results.previous()) {
                            int newQuestionID = results.getInt("Question_ID");
                            DatabaseInsert("UPDATE Poll SET Active_Question = " + newQuestionID + " WHERE Poll_ID =" + pid);
                            //closeQuestion(activeQuestion);
                            openQuestion(newQuestionID, seconds);
                            return newQuestionID;
                        }
                        return -1;
                    }
                }
            }
        } catch (Exception ex) {
            m_logger.info("getNextQuestion() failed\n");
            m_logger.info(ex.toString());
            return -1;
        }
        return 1;
    }
    
//    public final int[] getAttendance(pollID) {
//        String sql = "SELECT Count(User_ID) ";
//    }

    public ArrayList<PollUsers> getActiveUsers(int pollID) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
    
    
}
